Logistic Planning with Nonlinear Goal Programming Models in Spreadsheets

This is a case study of a coal mining company to demonstrate how algebra principles and nonlinear goal programming can be applied for logistics planning using spreadsheet software. The paper asserts that mathematical programming techniques are not well-accepted by managers because the models are difficult to understand due to abstract notational conventions yet alternative commercial software is inflexible (and sometimes inaccurate). The relevant operations research literature was reviewed, highlighting techniques applicable for analyzing quantitative and qualitative logistics data. A practical supply-demand transportation logistics model was built which included determinist constraints and stochastic costing theories, while applying both linear and nonlinear calculus slope principles. The formulae were explained in algebraic standard form (citing corresponding spreadsheet functions). The logistics problem was optimized, illustrating how 6 mining sites could supply 4 countries with sufficient coal to meet different electricity demand levels, surpassing the break-even goal and projecting annual revenue of over $34 billion. DOI: 10.4018/jal.2012100101 2 International Journal of Applied Logistics, 3(4), 1-14, October-December 2012 Copyright © 2012, IGI Global. Copying or distributing in print or electronic forms without written permission of IGI Global is prohibited. Logistics planning falls into the category of operations research where the models can be complex due to numerous goals, factors and conditions. Logistics managers often need to estimate multiple decision variables against the resource constraints to plan an optimal solution. Manuscripts in popular operations research journals rely on calculus theories (Zhang & Xu, 2010), multinomial systems of equations (Souza, Coelho, Ribas, Santos, & Merschmann, 2010), and matrix algebra (Kleijnen, Beers, & Nieuwenhuyse, 2010), which can be laborious to calculate without software. Commercial software can facilitate planning but often includes unspecified simulation distributions and masks assumptions that could produce unreliable estimates (without warning) if the model does not fit the operational data (Cochran, Cox, Keskinocak, Kharoufeh, & Smith, 2011). More so, commercial software applies a ‘black box’ one-size-fits-all philosophy. Theoretical disciplinary-specific logistical models are published in the literature but their formulae are difficult for managers to interpret due to the vector arithmetic and/or abstract calculus notational symbol system conventions (e.g., Leibniz, Newtonian, Euler, and Peano). In a sense this leaves logistics managers facing a paradox of buying commercial software that is irrelevant or inflexible (and often expensive), yet powerful mathematical models being freely available in the operations research literature but they are too complex to understand for applying to production data. Simpler customizable models are needed. The approach here is to build logistical decision making models for a coal mining supply-demand case study, using common spreadsheet software, which makes the formulas and variables accessible as well as customizable.